﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using QLDHS.DO;
namespace QLDHS.DAL
{
    class KYLUAT_DAL
    {

        OleDbConnection conn = Program.GetConnection();

        public DataSet loadAll()
        {
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
                string sql = "select * from KYLUAT";
                OleDbDataAdapter adp = new OleDbDataAdapter(sql, conn);
                adp.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

            return ds;

        }
        public DataSet loadMaNV()
        {
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
                string sql = "select MACB,HOTEN from HSNS";
                OleDbDataAdapter adp = new OleDbDataAdapter(sql, conn);
                adp.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

            return ds;

        }
        public int insertkl(KYLUAT_DO kl)
        {
            int kq = 0;
            try
            {
                using (OleDbCommand cmd = conn.CreateCommand())
                {
                    string text = "INSERT INTO KYLUAT VALUES (?,?,?,?,?,?)";
                    cmd.CommandText = text;
                    try
                    {
                        conn.Open();
                        cmd.Parameters.AddWithValue("@MAKYLUAT", kl.NvmaKL);
                        cmd.Parameters.AddWithValue("@MACB", kl.NvmaCB);
                        cmd.Parameters.AddWithValue("@SOLANKYLUAT", kl.Nvsolankyluat);
                        cmd.Parameters.AddWithValue("@NAMKYLUAT", kl.Nvnamkyluat);
                        cmd.Parameters.AddWithValue("@HINHTHUCKYLUAT", kl.Nvhinhthuckyluat);
                        cmd.Parameters.AddWithValue("@LYDO", kl.Nvlydo);
                        kq = cmd.ExecuteNonQuery();
                    }
                    catch (OleDbException ex)
                    {
                        throw ex;
                    }
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return kq;
        }
        public int updatekl(KYLUAT_DO kl)
        {
            int kq = 0;
            try
            {
               
                using (OleDbCommand cmd = conn.CreateCommand())
                {
                    string text = "update KYLUAT set MAKYLUAT=?,MACB=?,SOLANKYLUAT=?,NAMKYLUAT=?,HINHTHUCKYLUAT=?,LYDO=?";
                    cmd.CommandText = text;
                    try
                    {
                        conn.Open();
                        cmd.Parameters.AddWithValue("@MAKYLUAT", kl.NvmaKL);
                        cmd.Parameters.AddWithValue("@MACB", kl.NvmaCB);
                        cmd.Parameters.AddWithValue("@SOLANKYLUAT", kl.Nvsolankyluat);
                        cmd.Parameters.AddWithValue("@NAMKYLUAT", kl.Nvnamkyluat);
                        cmd.Parameters.AddWithValue("@HINHTHUCKYLUAT", kl.Nvhinhthuckyluat);
                        cmd.Parameters.AddWithValue("@LYDO", kl.Nvlydo);
                        kq = cmd.ExecuteNonQuery();
                    }
                    catch (OleDbException ex)
                    {
                        throw ex;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return kq;
        }
        public int  delKL(string makl)
        {
            int kqdel = 0;
            try
            {
                using (OleDbCommand cmd = conn.CreateCommand())
                {
                    string text = "DELETE FROM  KHENTHUONG WHERE MAKYLUAT=?";
                    cmd.CommandText = text;
                    try
                    {
                        conn.Open();
                        cmd.Parameters.AddWithValue("@MAKYLUAT", makl);
                        kqdel = cmd.ExecuteNonQuery();
                    }
                    catch (OleDbException ex)
                    {
                        throw ex;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

            return kqdel;
        }
    }
}
